/***********************************************************************************************************************************
 * Name:	SelfSignedSSL.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Check if SQL Server started up with a self-generated SSL certificate.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
GO
SET NOCOUNT ON;
DECLARE @ErrorLog TABLE ([LogDate] DATETIME NOT Null, [Source] SYSNAME NOT Null, [Message] NVARCHAR(4000) NOT Null);
INSERT INTO @ErrorLog EXECUTE master.sys.xp_readerrorlog;
IF EXISTS (SELECT * FROM @ErrorLog WHERE [Source] = N'Server' AND [Message] = N'A self-generated certificate was successfully loaded for encryption.') BEGIN;
  PRINT N'WARNING: SSL Certificate is Self-Generated';
  DECLARE @body NVARCHAR(255) = N'<p><b>ATTENTION:</b> <u>Signature Flight Support Global Service Desk</u></p><p>The SQL Server service on ' + @@SERVERNAME + N' is using a self-generated certificate. Many applications will not function without a trusted certificate in place.</p>';
  EXECUTE msdb.dbo.sp_send_dbmail
    @from_address	= N'SQL_Certificate@US.BBAAviation.com',
	@recipients		= N'Frank.Figearo@us.BBAAviation.com',
--	@recipients		= N'BBAServiceDesk@BBAAviation.com',
--	@copy_recipients= N'DBANotifications@US.BBAAviation.com',
	@subject		= N'SQL Server SSL Certificate Not Configured',
	@body_format	= N'HTML',
	@body			= @body,
	@importance		= N'High';
END;
ELSE PRINT N'Trusted SSL Certificate Found';
SET NOCOUNT OFF;
GO